---
title: "Themed dashboard"
output:
  flexdashboard::flex_dashboard:
    theme: 
      version: 4
      bootswatch: minty
    orientation: columns
    source_code: embed
---

```{r setup, include=FALSE}
library(flexdashboard)
library(ggplot2)
library(plotly)
library(tidyverse)
library(RMySQL)
library(DBI)
library(tidytext)
library(wordcloud)
```

```{r}
# Connect to a SQLite in-memory database
database <- dbConnect(MySQL(), 
                      dbname = "wdl_database",
                      host = "localhost",
                      user = "user",
                      password = "password",
                      port = 3306)

dfData <- dbGetQuery(database, "SELECT * FROM discord_messages")
```

```{r}
result_messagespermonth<- dbGetQuery(database, "
    SELECT
    UserId,
    Name,
    COUNT(MessageId) AS TotalMessages,
    DATE_FORMAT(Timestamp, '%Y-%m') AS Month
FROM
    wdl_database.discord_messages
WHERE
    Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje','lykozen','coeus._','coeus7680')
GROUP BY
    UserId, Name, DATE_FORMAT(Timestamp, '%Y-%m')
ORDER BY
    DATE_FORMAT(Timestamp, '%Y-%m');
")

result_totalmessages<- dbGetQuery(database, "
SELECT COUNT(*) as TotalMessages
FROM wdl_database.discord_messages;
")

result_mostmessages<- dbGetQuery(database, "
SELECT Name, COUNT(distinct Id) as MostMessages 
FROM wdl_database.discord_messages
GROUP BY Name
ORDER BY MostMessages DESC
LIMIT 1;
")

result_nitrodistrobutionperusers<- dbGetQuery(database, "
SELECT PremiumType, 
       GROUP_CONCAT(DISTINCT Name ORDER BY Name SEPARATOR ', ') AS Users, 
       COUNT(DISTINCT Name) AS UniqueUserCount
FROM wdl_database.discord_messages
GROUP BY PremiumType;
")

result_totalmessagesperuser<- dbGetQuery(database, "
    SELECT
    Name,
    COUNT(MessageId) AS TotalMessages
FROM
    wdl_database.discord_messages
WHERE
    Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje','lykozen','coeus._','coeus7680')
GROUP BY
    Name
HAVING
    COUNT(MessageId) >= 100;
")

result_leastmessages<- dbGetQuery(database, "
SELECT
    CASE 
        WHEN Name = 'coeus._' OR Name = 'coeus7680' THEN 'coeus'
        ELSE Name
    END AS NameAlias,
    COUNT(DISTINCT Id) as MostMessages
FROM
    wdl_database.discord_messages
WHERE
    Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje', 'lykozen', 'coeus._', 'coeus7680')
GROUP BY
    NameAlias
ORDER BY
    MostMessages ASC
LIMIT 1;
")

word_frequencies <- dfData %>% 
  unnest_tokens(word, Content) %>%  # Split text into words
  count(word, sort = TRUE)          # Count and sort by frequency
top_words <- head(word_frequencies, 50)
```

Value Boxes {data-width=100}
-------------------------------------

### Total Messages

```{r}
valueBox(result_totalmessages$TotalMessages, caption = "Total Messages", icon = "fa-comments")
```

### Most Messages

```{r}
valueBox(result_mostmessages$Name, caption = "Most Messages", color = "info", icon = "fa-comments")
```

### Least Messages
```{r}
valueBox(result_leastmessages$Name, caption = "Least Messages", color = "danger", icon = "fa-comments")
```

Column {data-width=500 .tabset}
-----------------------------------------------------------------------

### Monthly Messages

```{r}
plot_messagepermonth <- ggplot(result_messagespermonth, aes(x = Month, y = TotalMessages, fill = Name)) +
  geom_bar(stat = "identity") +
  coord_flip() +  # Flips the coordinates for horizontal bars
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) +  # Rotate x-axis labels if needed
  labs(title = "Monthly Messages per User", 
       x = "Month", 
       y = "Total Messages")

ggplotly(plot_messagepermonth)
```

### Total messages

```{r}
plot_totalmessagesperuser <- ggplot(result_totalmessagesperuser, aes(x = Name, y = TotalMessages, fill = Name)) +
  geom_bar(stat = "identity") +
  coord_flip() +  # Flips the coordinates for horizontal bars
  theme_minimal() +
  labs(title = "Total Messages per User",
       x = "",
       y = "Total Messages") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        legend.position = "none")  # Removes the legend

# Convert to an interactive plotly object
ggplotly(plot_totalmessagesperuser)
```




Column {data-height=500}
-----------------------------------------------------------------------

### Nitro Distrubution

```{r}
plot_ly(result_nitrodistrobutionperusers, labels = ~PremiumType, values = ~UniqueUserCount, type = 'pie',
  textinfo = 'label+percent', insidetextorientation = 'radial') %>%
  layout(title = 'Distribution of Premium Types Among Users')
```

### Top words

```{r}
# Assuming top_words is already created and contains the top 50 words
plot_topwords <- ggplot(top_words, aes(x = reorder(word, n), y = n, fill = word)) +
  geom_bar(stat = "identity") +
  coord_flip() +  # Flips the coordinates to make the plot horizontal
  scale_fill_viridis_d() +  # Use the viridis color palette for discrete data
  labs(title = "Top 50 Word Frequencies",
       x = "Words",
       y = "Frequency") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 90, hjust = 1),  # Adjust x-axis labels for readability
        legend.position = "none")  # Hide the legend

# Convert to an interactive plotly object
ggplotly(plot_topwords)
```